CD Actual 80/CD Actual 80 Julio-Agosto 2003.iso/Linux/LinuxGazette/lg/issue72/misc/nielsen/sql/students.table_functions

Text File | 2002-08-14 | 21.1 KB | 650 lines

drop sequence students_sequence;
create sequence students_sequence;
drop sequence students_sequence_backup;
create sequence students_sequence_backup;
drop table students;
create table students (
students_id int4 NOT NULL PRIMARY KEY DEFAULT nextval('students_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
users_id int4 not null default 0  REFERENCES users
       ON DELETE NO ACTION
       ON UPDATE CASCADE ,
class_id int4 not null default 0  REFERENCES class
       ON DELETE NO ACTION
       ON UPDATE CASCADE
);drop table students_backup;
create table students_backup (
backup_id int4 NOT NULL UNIQUE DEFAULT nextval('students_sequence_backup'),
    students_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    users_id int4 not null default 0,
class_id int4 not null default 0, error_code text NOT NULL DEFAULT ''
);
drop view students_active;
create view students_active as select * from students
        where active = 1;
drop view students_deleted;
create view students_deleted as select * from students
        where active = 0;
drop view students_backup_ids;
create view students_backup_ids as
           select distinct students_id from students_backup;
drop view students_purged;
create view students_purged as
   select * from students_backup where oid = ANY (
     select max(oid) from students_backup where students_id = ANY
        (
        select distinct students_id from students_backup
          where students_backup.error_code = 'purge'
           and NOT students_id = ANY (select students_id from students)
        )
        group by students_id
     )
    ;
---              Generic Functions for Perl/Postgresql version 1.0

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl
---    Copyright notice.
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.
-- create a method to purge one item.
--  \i /tmp/Test/sample/students.table
---------------------------------------------------------------------

drop function sql_students_insert ();
CREATE FUNCTION sql_students_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into students (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted.
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the students id.
   FOR record1 IN SELECT students_id FROM students where oid = oid1
      LOOP
      id := record1.students_id;
   END LOOP;

     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id < 1 THEN return (-2); END IF;

      -- Now backup the data.
    FOR record_backup IN SELECT * FROM students where students_id = id
       LOOP
       insert into students_backup (students_id, date_updated, date_created,
           active, error_code)
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as students_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_students_delete (int4);
CREATE FUNCTION sql_students_delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 0.
   FOR record1 IN SELECT students_id FROM students
          where students_id = id
      LOOP
      update students set active=0, date_updated = CURRENT_TIMESTAMP
           where students_id = id;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      id_exists := 1;
   END LOOP;

     -- If we did not find the id, abort and return -2.
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM students where students_id = id
      LOOP
      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (record_backup.students_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''delete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed.
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------

drop function sql_students_undelete (int4);
CREATE FUNCTION sql_students_undelete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 1.
   FOR record1 IN SELECT students_id FROM students
          where students_id = id
      LOOP
      update students set active=1, date_updated = CURRENT_TIMESTAMP
           where students_id = id;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      id_exists := 1;
   END LOOP;

     -- If we did not find the id, abort and return -2.
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM students where students_id = id
      LOOP
      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (record_backup.students_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''undelete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed.
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_students_update (int4 , int4, int4);
CREATE FUNCTION sql_students_update  (int4 , int4, int4)
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
              var_2 int4;
          var_3 int4;

BEGIN
             var_2 := clean_numeric($2);
         var_3 := clean_numeric($3);

     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record_update IN SELECT students_id FROM students
         where students_id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update students set date_updated = CURRENT_TIMESTAMP
      , users_id = var_2, class_id = var_3
        where students_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM students where students_id = id
      LOOP
     insert into students_backup (students_id,
         date_updated, date_created, active
         , users_id, class_id, error_code)
       values (record_update.students_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         , record_backup.users_id, record_backup.class_id, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_students_copy (int4);
CREATE FUNCTION sql_students_copy (int4)
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;
    return_int4 int4 := 0;
    id_new int4 := 0;
    students_new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record1 IN SELECT students_id FROM students where students_id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql_students_insert() as students_insert
      LOOP
      students_new := record1.students_insert;
   END LOOP;
     -- If the students_new is not greater than 0, return error.
   IF students_new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM students where students_id = id
      LOOP

     FOR record1 IN SELECT sql_students_update(students_new , clean_text(record2.users_id), clean_text(record2.class_id))
        as students_insert
      LOOP
        -- execute some arbitrary command just to get it to pass.
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.
   return (students_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_students_purge ();
CREATE FUNCTION sql_students_purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM students where active = 0
      LOOP
         -- Record the id we want to delete.
      delete_id = record_backup.students_id;

      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (record_backup.students_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''purge''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from students where students_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_students_purgeone (int4);
CREATE FUNCTION sql_students_purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id < 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM students
      where active = 0 and students_id = delete_id
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM students where students_id = delete_id
      LOOP

      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (record_backup.students_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from students where students_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
drop function sql_students_unpurge ();
CREATE FUNCTION sql_students_unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged.
   FOR record1 IN select distinct students_id from students_backup
       where students_backup.error_code = ''purge''
          and NOT students_id = ANY (select students_id from students)
      LOOP

      purged_id := record1.students_id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date.

      FOR record2 IN select max(oid) from students_backup
          where students_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
      END LOOP;

         -- If the oid_found is 0, return error.
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them.
      FOR record_backup IN select * from students_backup
          where oid = highest_oid
        LOOP

      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (purged_id, record_backup.date_updated,
             timestamp1, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''unpurge''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;

      insert into students (students_id, date_updated, date_created,
          active , users_id, class_id)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             , record_backup.users_id, record_backup.class_id );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- We got this far, it must be true, return how many were affected.
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_students_unpurgeone (int4);
CREATE FUNCTION sql_students_unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct students_id from students_backup
       where students_backup.error_code = ''purge''
          and NOT students_id = ANY (select students_id from students)
          and students_id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Now find the highest oid.
   FOR record2 IN select max(oid) from students_backup
          where students_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it.
    FOR record_backup IN select * from students_backup
          where oid  = highest_oid
        LOOP
        -- Insert into backup that it was unpurged.
      insert into students_backup (students_id, date_updated, date_created,
          active , users_id, class_id ,error_code)
           values (purged_id, timestamp1,
             record_backup.date_created, record_backup.active
             , record_backup.users_id, record_backup.class_id , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insert into live table.
      insert into students (students_id, date_updated, date_created,
          active , users_id, class_id)
           values (record_backup.students_id, timestamp1,
             record_backup.date_updated, record_backup.active
             , record_backup.users_id, record_backup.class_id );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

insert into students (students_id, date_updated, date_created, active)
    values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
insert into students_backup (backup_id, students_id,
     date_updated, date_created, active, error_code)
    values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation');




drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front.
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end.
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space.
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (11111);



select sql_students_insert();
select sql_students_update(1,1,1);

select sql_students_insert();
select sql_students_update(2,2,1);

select sql_students_insert();
select sql_students_update(3,1,2);

select sql_students_insert();
select sql_students_update(4,2,2);












vacuum;